/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apus.persistence.jdbc;

import apus.entity.Call;
import apus.entity.PhoneNumber;
import apus.entity.RealCall;
import apus.entity.RealPhoneNumber;
import apus.entity.Subscriber;
import apus.exception.EntityNotFoundException;
import apus.exception.DatabaseException;
import apus.persistence.PhoneNumberDAO;
import apus.persistence.proxy.CallProxy;
import apus.persistence.proxy.OrganizationProxy;
import apus.persistence.proxy.PersonProxy;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Roman Dyatkovsky
 * @author Maxim Vasilevsky
 * @since APUS v0.5
 */
public class JdbcPhoneNumberDAO extends AbstractJdbcDAO<PhoneNumber> implements PhoneNumberDAO {

    @Override
    public void create(PhoneNumber entity) {
        entity.setId(getNextId());
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("INSERT INTO PHONE_NUMBER (ID_PHONE_NUMBER, SUBSCRIBER_ID, PHONENUMBER) "
                    + "VALUES (?,?,?)");
            st.setInt(1, entity.getId());
            st.setInt(2, entity.getOwner().getId());
            st.setString(3, entity.getNumber());
            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't create new PhoneNumber: " + entity.toString(), e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public PhoneNumber read(int id) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        PhoneNumber entity = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT SUBSCRIBER_ID, PHONENUMBER "
                    + "FROM PHONE_NUMBER "
                    + "WHERE ID_PHONE_NUMBER=" + id);
            if (rs.next()) {
                int subId = rs.getInt("SUBSCRIBER_ID");
                String num = rs.getString("PHONENUMBER").trim();
                entity = new RealPhoneNumber(num);
                entity.setId(id);
                rs2 = st.executeQuery("SELECT SUBSCRIBER_TYPE "
                        + "FROM SUBSCRIBER "
                        + "WHERE ID_SUBSCRIBER=" + subId);
                rs2.next();
                int typeSub = rs2.getInt("SUBSCRIBER_TYPE");
                Subscriber sub = null;
                if (subId != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subId);
                }
                entity.setOwner(sub);

                Call call;
                rs2 = st.executeQuery("SELECT ID_CALL FROM PHONE_CALL WHERE NUMBER_FROM_ID=" + id);
                while (rs2.next()) {
                    call = new CallProxy();
                    call.setId(rs2.getInt("ID_CALL"));
                    entity.addOutCall(call);
                }

                rs2 = st.executeQuery("SELECT ID_CALL FROM PHONE_CALL WHERE NUMBER_TO_ID=" + id);
                while (rs2.next()) {
                    call = new CallProxy();
                    call.setId(rs2.getInt("ID_CALL"));
                    entity.addInCall(call);
                }


            }
        } catch (SQLException e) {
            throw new DatabaseException("Can't read PhoneNumber with ID=" + id, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }

        if (entity == null) {
            throw new EntityNotFoundException("PhoneNumber with ID=" + id + " not found");
        }
        return entity;
    }

    @Override
    public void update(PhoneNumber entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("UPDATE PHONE_NUMBER SET SUBSCRIBER_ID=?, PHONENUMBER=? "
                    + " WHERE ID_PHONE_NUMBER=?");
            st.setInt(1, entity.getOwner().getId());
            st.setString(2, entity.getNumber());
            st.setInt(3, entity.getId());
            st.execute();

        } catch (SQLException e) {
            throw new DatabaseException("Can't update PhoneNumber with ID=" + entity.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public void delete(PhoneNumber entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("DELETE FROM PHONE_NUMBER WHERE ID_PHONE_NUMBER=?");
            st.setInt(1, entity.getId());
            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't delete phoneNumber with ID=" + entity.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public List<PhoneNumber> findAll() {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        List<PhoneNumber> phoneNumberList = new ArrayList<>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT * FROM PHONE_NUMBER");
            int typeSub;
            int id;

            while (rs.next()) {
                PhoneNumber phoneNumber;
                id = rs.getInt("ID_PHONE_NUMBER");
                int subId = rs.getInt("SUBSCRIBER_ID");
                rs3 = st.executeQuery("SELECT SUBSCRIBER_TYPE FROM SUBSCRIBER WHERE ID_SUBSCRIBER=" + subId);
                rs3.next();
                typeSub = rs3.getInt("SUBSCRIBER_TYPE");
                String num = rs.getString("PHONENUMBER").trim();

                phoneNumber = new RealPhoneNumber();
                phoneNumber.setId(id);
                phoneNumber.setNumber(num);

                Subscriber sub = null;

                if (subId != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subId);
                }
                phoneNumber.setOwner(sub);

                Call call;
                rs2 = st.executeQuery("SELECT ID_CALL FROM PHONE_CALL WHERE NUMBER_FROM_ID=" + id);
                while (rs2.next()) {
                    call = new CallProxy();
                    call.setId(rs2.getInt("ID_CALL"));
                    phoneNumber.addOutCall(call);
                }

                rs2 = st.executeQuery("SELECT ID_CALL FROM PHONE_CALL WHERE NUMBER_TO_ID=" + id);
                while (rs2.next()) {
                    call = new CallProxy();
                    call.setId(rs2.getInt("ID_CALL"));
                    phoneNumber.addInCall(call);
                }

                phoneNumberList.add(phoneNumber);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read phoneNumber list", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs2.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (phoneNumberList.isEmpty()) {
            throw new EntityNotFoundException("PhoneNumbers not found");
        }
        return phoneNumberList;
    }

    @Override
    public PhoneNumber readByNumber(String number) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        PhoneNumber entity = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT ID_PHONE_NUMBER, SUBSCRIBER_ID "
                    + "FROM PHONE_NUMBER "
                    + "WHERE PHONENUMBER=" + number);
            if (rs.next()) {
                int subId = rs.getInt("SUBSCRIBER_ID");
                int id = rs.getInt("ID_PHONE_NUMBER");
                entity = new RealPhoneNumber(number);
                entity.setId(id);
                rs = st.executeQuery("SELECT SUBSCRIBER_TYPE "
                        + "FROM SUBSCRIBER "
                        + "WHERE ID_SUBSCRIBER=" + subId);
                rs.next();
                int typeSub = rs.getInt("SUBSCRIBER_TYPE");
                Subscriber sub = null;
                if (subId != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subId);
                }
                entity.setOwner(sub);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Can't read PhoneNumber " + number, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }

        if (entity == null) {
            throw new EntityNotFoundException("PhoneNumber " + number + " not found");
        }
        return entity;
    }

    @Override
    public List<PhoneNumber> findBySubscriber(String name) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        List<PhoneNumber> list = new ArrayList<>();
        PhoneNumber entity;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT ID_PHONE_NUMBER, PHONENUMBER, SUBSCRIBER_ID "
                    + "FROM PHONE_NUMBER "
                    + "JOIN SUBSCRIBER ON ID_SUBSCRIBER=SUBSCRIBER_ID " +
                    "WHERE lower(SUBSCRIBER_NAME) like '%"+ name.toLowerCase() +"%'");
            while (rs.next()) {
                int subId = rs.getInt("SUBSCRIBER_ID");
                int id = rs.getInt("ID_PHONE_NUMBER");
                entity = new RealPhoneNumber(rs.getString("PHONENUMBER"));
                entity.setId(id);
                
                rs2 = st.executeQuery("SELECT SUBSCRIBER_TYPE "
                        + "FROM SUBSCRIBER "
                        + "WHERE ID_SUBSCRIBER=" + subId);
                rs2.next();
                int typeSub = rs2.getInt("SUBSCRIBER_TYPE");
                Subscriber sub = null;
                if (subId != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subId);
                }
                entity.setOwner(sub);
                list.add(entity);
            }
            
            // + by number
            rs = st.executeQuery("SELECT ID_PHONE_NUMBER, PHONENUMBER, SUBSCRIBER_ID "
                    + "FROM PHONE_NUMBER "
                    + "WHERE lower(PHONENUMBER) like '%"+ name.toLowerCase() +"%'");
            while (rs.next()) {
                int subId = rs.getInt("SUBSCRIBER_ID");
                int id = rs.getInt("ID_PHONE_NUMBER");
                entity = new RealPhoneNumber(rs.getString("PHONENUMBER"));
                entity.setId(id);
                
                rs2 = st.executeQuery("SELECT SUBSCRIBER_TYPE "
                        + "FROM SUBSCRIBER "
                        + "WHERE ID_SUBSCRIBER=" + subId);
                rs2.next();
                int typeSub = rs2.getInt("SUBSCRIBER_TYPE");
                Subscriber sub = null;
                if (subId != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subId);
                }
                entity.setOwner(sub);
                list.add(entity);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Can't find Phone numbers", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }

        if (list.isEmpty()) {
            throw new EntityNotFoundException("Matches not found");
        }
        return list;
    }
}
